﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class AppraisalBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(int apr_seq, int emp_seq, int apr_form_seq, decimal score, int score_level_seq
                                    , int promote, string remarks, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);
                dbManager.AddParameters(0, "@apr_seq", apr_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@apr_form_seq", apr_form_seq);
                dbManager.AddParameters(3, "@score", score);
                dbManager.AddParameters(4, "@score_level_seq", score_level_seq);
                dbManager.AddParameters(5, "@promote", promote);
                dbManager.AddParameters(6, "@remarks", remarks);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", created_by);
                dbManager.AddParameters(9, "@created_when", DateTime.Now);

                string strSQL = "INSERT INTO HRM_APR_APPRAISAL_DTLS"
                    + "(APR_SEQ, EMP_SEQ, APR_FORM_SEQ, SCORE, SCORE_LEVEL_SEQ, PROMOTE"
                    + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN) "
                    + "VALUES(@apr_seq, @emp_seq, @apr_form_seq, @score, @score_level_seq "
                    + " ,@promote, @remarks, @record_status, @created_by, @created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_hrm_apr_appraisal_dtls}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void Update(int apr_seq, int emp_seq, int apr_form_seq, decimal score
            , int score_level_seq, int promote, string remarks, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@apr_seq", apr_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@apr_form_seq", apr_form_seq);
                dbManager.AddParameters(3, "@score", score);
                dbManager.AddParameters(4, "@score_level_seq", score_level_seq);
                dbManager.AddParameters(5, "@promote", promote);
                dbManager.AddParameters(6, "@remarks", remarks);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@updated_by", updated_by);
                dbManager.AddParameters(9, "@updated_when", DateTime.Now);

                string strSQL = " UPDATE  hrm_apr_appraisal_dtls ("
                    + "SET "
                    + " apr_seq =@apr_seq"
                    + " ,emp_seq=@emp_seq "
                    + " ,apr_form_seq=@apr_form_seq "
                    + " ,score=@score "
                    + " ,score_level_seq=@score_level_seq "
                    + " ,promote=@promote "
                    + " ,remarks=@remarks "
                    + " ,record_status=@record_status "
                    + " ,updated_by=@updated_by "
                    + " ,updated_when=@updated_when"
                    + " WHERE apr_dtls_seq =@apr_seq"
                    ;
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void Delete(int apr_dtls_seq, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@apr_dtls_seq", apr_dtls_seq);
                dbManager.AddParameters(1, "@updated_by", updated_by);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);

                string sql;
                sql = " Update hrm_apr_appraisal_dtls set " +
                    " record_status = @record_status, " +
                    " updated_by = @updated_by " +
                    " WHERE apr_dtls_seq = @apr_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public DataTable select()
        //{

        //    try
        //    {
        //        string sql;
        //        sql = " SELECT " +
        //                " hrm_prm_promotion_employees.prm_emp_seq, " +
        //                " mas_name_prefixes.prefix_name, " +
        //                " hrm_ps_employees.first_name, " +
        //                " hrm_ps_employees.last_name, " +
        //                " hrm_mp_positions.pos_name, " +
        //                " hrm_mp_classes.class_name, " +
        //                " hrm_mp_job_sites3.job_site_name, " +
        //                " case hrm_prm_promotion_employees.appraisal when 1 then 'ประเมิน' when 0 then 'ไม่ประเมิน' else '' end as appraisal, " +
        //                " hrm_mp_job_sites2.job_site_name as apr_job_site_name, " +
        //                " case hrm_prm_promotion_employees.promote when 1 then 'เลื่อน' when 0 then 'ไม่เลื่อน' else '' end as promote, " +
        //                " hrm_mp_job_sites.job_site_name as prm_job_site_name " +
        //                " FROM " +
        //                " hrm_prm_promotion_employees " +
        //                " Inner Join hrm_ps_employees ON hrm_prm_promotion_employees.emp_seq = hrm_ps_employees.emp_seq " +
        //                " Inner Join hrm_prm_promotions ON hrm_prm_promotion_employees.prm_seq = hrm_prm_promotions.prm_seq " +
        //                " Inner Join hrm_mp_job_sites ON hrm_prm_promotion_employees.prm_job_site_seq = hrm_mp_job_sites.job_site_seq " +
        //                " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
        //                " Inner Join hrm_mp_classes ON hrm_ps_employees.class_seq = hrm_mp_classes.class_seq " +
        //                " Inner Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_prm_promotion_employees.apr_job_site_seq = hrm_mp_job_sites2.job_site_seq " +
        //                " Inner Join mas_name_prefixes ON hrm_ps_employees.name_prefix_seq = mas_name_prefixes.name_prefix_seq " +
        //                " Inner Join hrm_mp_job_sites AS hrm_mp_job_sites3 ON hrm_ps_employees.working_job_site_seq = hrm_mp_job_sites3.job_site_seq " +
        //                " WHERE " +
        //                " 1=2 ";

        //        //where emp_seq = " + emp_seq;
        //        DataSet ds;
        //        DataTable dt;
        //        ds = new DataSet();
        //        dbManager.Open();
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0];
        //        return dt;
        //    }
        //    catch
        //    {

        //        return null;
        //        // throw new Exception("");
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();  
        //    }
        //}

        public string GetDepartName(int EmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_seq", EmpSeq);
                string sql;
                sql = " SELECT hrm_mp_job_sites2.job_site_name " +
                        " FROM (SELECT " +
                        " 	ifNULL(hrm_ps_employees.working_job_site_seq,  " +
                        " 	hrm_mp_positions.job_site_seq) as working_job_site_seq  " +
                        " 	FROM  " +
                        " 	hrm_ps_employees  " +
                        " 	Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " 	WHERE hrm_ps_employees.emp_seq = @emp_seq) as T1  " +
                        " 	Inner Join hrm_mp_job_sites ON T1.working_job_site_seq = hrm_mp_job_sites.job_site_seq " +
                        " 	Inner Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_mp_job_sites.upper_job_site_seq = hrm_mp_job_sites2.job_site_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0].Rows[0]["job_site_name"].ToString();
            }
            catch
            {
                return "";
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetJobSiteSeq(int EmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_seq", EmpSeq);
                string sql;
                sql = " SELECT " +
                        " ifNULL(hrm_ps_employees.working_job_site_seq, " +
                        " hrm_mp_positions.job_site_seq) as working_job_site_seq " +
                        " FROM " +
                        " hrm_ps_employees " +
                        " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " WHERE " +
                        " hrm_ps_employees.emp_seq = @emp_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["working_job_site_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetYearType(int empTypeSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_type_seq", empTypeSeq);
                string sql;
                sql = " SELECT " +
                        " hrm_ps_employee_types.year_type " +
                        " FROM " +
                        " hrm_ps_employee_types " +
                        " WHERE " +
                        " hrm_ps_employee_types.emp_type_seq =  @emp_type_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["year_type"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable select(int PrmYear, int prmPeriod, int empTypeSeq, int JobSiteSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@prm_year", PrmYear);
                dbManager.AddParameters(1, "@prm_period", prmPeriod);
                dbManager.AddParameters(2, "@emp_type_seq", empTypeSeq);
                dbManager.AddParameters(3, "@job_site_seq", "%/" + JobSiteSeq.ToString() + "/%");
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                string strSQL;

                strSQL = "SELECT t.* FROM("
                        + "SELECT e.EMP_SEQ, CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS EMP_TEXT"
                         + ", IFNULL(dt.SCORE, pe.SCORE) AS SCORE, pe.APR_DTLS_SEQ, "
                         + " p.POS_NAME, c.CLASS_NAME, IFNULL(wj.JOB_SITE_NAME, js.JOB_SITE_NAME) AS APR_JOB_SITE_NAME,"
                         + " case pe.promote when 1 then 'เลื่อน' when 0 then 'ไม่เลื่อน' else '' end as PROMOTE_NAME,"
                         + " pe.PROMOTE, IFNULL(e.WORKING_JOB_SITE_SEQ, p.JOB_SITE_SEQ) AS APR_JOB_SITE_SEQ"
                         + " FROM HRM_PS_EMPLOYEES e"
                         + " LEFT JOIN HRM_PRM_PROMOTION_EMPLOYEES pe ON e.EMP_SEQ = pe.EMP_SEQ"
                         + " LEFT JOIN HRM_PRM_PROMOTIONS pr ON pe.PRM_SEQ = pr.PRM_SEQ"
                            + " AND pr.PRM_YEAR = @prm_year"
                            + " AND pr.PRM_PERIOD = @prm_period"
                         + " LEFT JOIN HRM_APR_APPRAISAL_DTLS dt ON pe.APR_DTLS_SEQ = dt.APR_DTLS_SEQ"
                         + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                         + " LEFT JOIN HRM_MP_JOB_SITES js ON p.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                         + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                         + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                         + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                         + " WHERE e.RECORD_STATUS not like @RecordStatus "
                            + " AND e.EMP_TYPE_SEQ = @emp_type_seq "
                        + ")t"
                        + " LEFT JOIN HRM_MP_JOB_SITES jj ON t.APR_JOB_SITE_SEQ = jj.JOB_SITE_SEQ"
                        + " WHERE jj.JOB_SITE_FULL_PATH LIKE @job_site_seq";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetEmployeeTypes()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = "SELECT EMP_TYPE_SEQ, EMP_TYPE_NAME, REMARKS, CREATED_BY, UPDATED_BY"
                    + " FROM HRM_PS_EMPLOYEE_TYPES"
                    + " WHERE RECORD_STATUS not like @DeleteRecord";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable GetAppraisals(int Year)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@apr_year", Year);

                //string strSQL = " SELECT " +
                //                " hrm_apr_appraisals.apr_period, " +
                //                " concat(hrm_apr_appraisals.apr_name,'  ', " +
                //                " cast(hrm_apr_appraisals.act_start_date as char),' ถึง ', " +
                //                " cast(hrm_apr_appraisals.act_end_date as char)) as apr_name " +
                //                " FROM " +
                //                " hrm_apr_appraisals " +
                //                " WHERE " +
                //                " hrm_apr_appraisals.apr_year =  @apr_year " +
                //                " Order By apr_period";
                string strSQL = " SELECT " +
                                " hrm_apr_appraisals.apr_period, " +
                                " hrm_apr_appraisals.apr_name, " +
                                " hrm_apr_appraisals.act_start_date, " +
                                " hrm_apr_appraisals.act_end_date " +
                                " FROM  " +
                                " hrm_apr_appraisals  " +
                                " WHERE  " +
                                " hrm_apr_appraisals.apr_year =  @apr_year " +
                                " Order By apr_period";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetYearTypeName(int yearTypeSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@year_type_seq", yearTypeSeq);
                string sql;
                sql = " SELECT " +
                        " sys_year_type.year_type_name " +
                        " FROM " +
                        " sys_year_type " +
                        " Inner Join hrm_ps_employee_types ON hrm_ps_employee_types.year_type_seq = sys_year_type.year_type_seq " +
                        " WHERE " +
                        " hrm_ps_employee_types.emp_type_seq = @year_type_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0].Rows[0]["year_type_name"].ToString();
            }
            catch
            {
                return "";
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
